/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package getsinanowdata;

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author ryu
 */
public class dbase {

    String ip;
    String user;
    String pw;
    String dbName;
    String cUrl;

    /**
     * 数据库连接
     * @param ip
     * @param user
     * @param pw
     * @param dbName 
     */
    public dbase(String ip, String user, String pw, String dbName) {
        this.ip = ip;
        this.user = user;
        this.pw = pw;
        this.dbName = dbName;
        cUrl = "jdbc:sqlserver://" + this.ip + ";databaseName=" + this.dbName + ";user=" + this.user + ";password=" + this.pw;
    }

    public List<String> getGpids() throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        List<String> dataarray;
        try (Connection connection = DriverManager.getConnection(this.cUrl); Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            dataarray = new ArrayList<>();
            ResultSet rs = statement.executeQuery("select gpid from dbo.orders where status=1 and getdate()<overTime and getdate()>startTime group by gpid order by gpid");
            if (!rs.isClosed()) {
                while (rs.next()) {
                    String temp = rs.getString("gpid").trim();
                    if (temp.charAt(0) == '6') {
                        temp = "sh" + temp;
                    } else {
                        temp = "sz" + temp;
                    }
                    dataarray.add(temp);
                }
                //System.out.println("count:" + dataarray.size());
            } else {
                System.out.println("no data!");
            }
        }
        return dataarray;
    }

    public void insertData(List<data> gpdatas) throws ClassNotFoundException, SQLException {
        long test = System.currentTimeMillis();
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection connection = DriverManager.getConnection(this.cUrl);
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);  // set timeout to 30 sec.
        SQLServerDataTable sourceDataTable = new SQLServerDataTable();

        if ((gpdatas != null) && (!gpdatas.isEmpty())) {
            sourceDataTable.addColumnMetadata("id", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("getdate", java.sql.Types.INTEGER);
            sourceDataTable.addColumnMetadata("gettime", java.sql.Types.INTEGER);
            sourceDataTable.addColumnMetadata("gpid", java.sql.Types.INTEGER);
            sourceDataTable.addColumnMetadata("gpname", java.sql.Types.NVARCHAR);
            sourceDataTable.addColumnMetadata("openprice", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("oldprice", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("price", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("maxprice", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("minprice", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sumvol", java.sql.Types.INTEGER);
            sourceDataTable.addColumnMetadata("amount", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("avgprice", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("bv1", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("bp1", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("bv2", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("bp2", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("bv3", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("bp3", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("bv4", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("bp4", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("bv5", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("bp5", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sv1", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("sp1", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sv2", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("sp2", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sv3", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("sp3", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sv4", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("sp4", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("sv5", java.sql.Types.BIGINT);
            sourceDataTable.addColumnMetadata("sp5", java.sql.Types.REAL);
            sourceDataTable.addColumnMetadata("gpdate", java.sql.Types.INTEGER);
            sourceDataTable.addColumnMetadata("gptime", java.sql.Types.INTEGER);
            long id;
            for (data dt : gpdatas) {
                id = dt.getdate;
                id = id * 1000000 + dt.gettime;
                sourceDataTable.addRow(id, dt.getdate, dt.gettime, dt.gpid, dt.gpname, dt.openprice, dt.oldprice, dt.price, dt.maxprice, dt.minprice, dt.sumvol,
                        dt.amount, dt.avgprice, dt.bv1, dt.bp1, dt.bv2, dt.bp2, dt.bv3, dt.bp3, dt.bv4, dt.bp4, dt.bv5, dt.bp5, dt.sv1, dt.sp1, dt.sv2, dt.sp2,
                        dt.sv3, dt.sp3, dt.sv4, dt.sp4, dt.sv5, dt.sp5, dt.gpdate, dt.gptime);
            }
        }

        if ((gpdatas != null) && (!gpdatas.isEmpty())) {
            statement.executeUpdate("truncate table dbo.datas");
            SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement(
                    "INSERT INTO dbo.datas SELECT * FROM ?;");
            pStmt.setStructured(1, "dbo.DataTableType", sourceDataTable);
            pStmt.execute();
        }
        statement.close();
        connection.close();
        System.out.println("入库时间:" + (System.currentTimeMillis() - test));
    }

    public void analysis() throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        try (Connection connection = DriverManager.getConnection(this.cUrl); Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            statement.executeUpdate("exec dbo.analysis");//进行条件指令分析执行
        }
    }

}
